package com.cs410.collabwriting.main;

import java.util.ArrayList;
import java.util.HashMap;

import com.cs410.collabwriting.tools.DatabaseManager;
import com.cs410.collabwriting.tools.queryTools.DatabaseTable;
import com.cs410.collabwriting.tools.queryTools.QueryBuilder;
import com.cs410.collabwriting.tools.queryTools.QueryCondition;
import com.cs410.collabwriting.tools.queryTools.QueryType;
import com.cs410.collabwriting.tools.queryTools.tables.COMMENT_TABLE;
import com.cs410.collabwriting.tools.queryTools.tables.POST_TABLE;
import com.cs410.collabwriting.tools.queryTools.tables.PROMPT_TABLE;
import com.cs410.collabwriting.tools.queryTools.tables.STORYLISTNAME_TABLE;
import com.cs410.collabwriting.tools.queryTools.tables.STORYLIST_TABLE;
import com.cs410.collabwriting.tools.queryTools.tables.STORY_TABLE;
import com.cs410.collabwriting.tools.queryTools.tables.TAG_TABLE;
import com.cs410.collabwriting.tools.queryTools.tables.UPVOTE_TABLE;

public class StoryManager implements Manager {

	private static StoryManager m_storyManager = null;
	private HashMap<Integer,Story> m_allStories = null; // <StoryID, StoryObject>
	
	
	private StoryManager() {
		m_allStories = new HashMap<Integer, Story>();
	}
	
	//For singleton StoryManager
	public static StoryManager getInstance() {
		if( m_storyManager == null ) {
			m_storyManager = new StoryManager();
		}
		return m_storyManager;
	}
	
	//Returns the stories in a particular story list, specified by its story list id
    public ArrayList<Story> getStoriesInList(String listid){
        
        ArrayList<Story> storiesInList = new ArrayList<Story>();
        
        ArrayList<String[]> storyIDsInList = DatabaseManager.executeQuery(
                new QueryBuilder(QueryType.SELECTION)
                .addColumns( STORYLIST_TABLE.STORYID )
                .addTables( DatabaseTable.STORYLIST_TABLE )
                .addConditions( QueryCondition.AND, STORYLIST_TABLE.LISTID, QueryCondition.EQ,  String.valueOf(listid) ) 
                .value() );
        
        if( storyIDsInList != null ) {
            StoryManager sM = StoryManager.getInstance();
            
            for( int i = 0; i < storyIDsInList.size(); i++ ) {
                
                int storyId = Integer.parseInt( storyIDsInList.get( i )[0] );
                storiesInList.add( sM.getStoryByID( storyId ) );
            }           
        }
        
        return storiesInList;
    }

	public ArrayList<String[]> getMostRecentStoryUpdates() {
		ArrayList<String[]> storyUpdates = DatabaseManager.executeQueryWithColumnNames( 
				new QueryBuilder( QueryType.SELECTION)
				.addColumns( DatabaseTable.STORY_TABLE + "." + STORY_TABLE.STORYID, DatabaseTable.POST_TABLE + "." + POST_TABLE.USERID + " AS FAVUSERID"  )
				.addColumns( DatabaseTable.POST_TABLE + "." + POST_TABLE.TIMESTAMP , DatabaseTable.STORY_TABLE + "." + STORY_TABLE.STORYTITLE )
				.addColumns( DatabaseTable.POST_TABLE + "." + POST_TABLE.POST )
				.addTables( DatabaseTable.STORY_TABLE, DatabaseTable.POST_TABLE )
				.addConditions( QueryCondition.AND, DatabaseTable.STORY_TABLE + "." + STORY_TABLE.STORYID, QueryCondition.EQ, DatabaseTable.POST_TABLE + "." + POST_TABLE.STORYID )
				.addAdditionalCriteria("ORDER BY POST_TABLE.TIMESTAMP DESC LIMIT 10" ) );
		ArrayList<String[]> newList = new ArrayList<String[]>();
		if( storyUpdates != null && storyUpdates.size() > 0 ) {
			String[] set = storyUpdates.get( 0 );
			for( int i = 0; i < set.length; i++ ) {
				set[i] = set[i].toLowerCase();
			}
			newList.add( set );
			for( int i = storyUpdates.size() - 1; i > 0 && i >= storyUpdates.size() - 11; i-- ) {
				newList.add( storyUpdates.get( i ) );
			}
		}
		return newList;
	}
	
	@Override
	//Returns all Story objects
	public ArrayList<DatabaseObjects> getAll() {
		
		ArrayList<DatabaseObjects> storyList = new ArrayList<DatabaseObjects>();
		
		ArrayList<String[]> allStoryIDsList = DatabaseManager.executeQuery(
			new QueryBuilder(QueryType.SELECTION)
			.addColumns(DatabaseTable.STORY_TABLE + "." + STORY_TABLE.STORYID)
			.addTables(DatabaseTable.STORY_TABLE, DatabaseTable.POST_TABLE)
			.addConditions(QueryCondition.AND, DatabaseTable.STORY_TABLE + "." + STORY_TABLE.STORYID, QueryCondition.EQ, DatabaseTable.POST_TABLE + "." + POST_TABLE.STORYID )
			.addAdditionalCriteria("GROUP BY STORY_TABLE.STORYID")
			.addAdditionalCriteria("ORDER BY POST_TABLE.TIMESTAMP ASC")
			.value() );	
		
		if( allStoryIDsList != null ) {
			for (int i= 0; i < allStoryIDsList.size(); i++){
				int IDstory = Integer.valueOf(allStoryIDsList.get(i)[0]);
				Story story;
				story = this.getStoryByID(IDstory);
				storyList.add(story);
			}
		}		
		return storyList;
	}
	
	// Creates a new story and adds it to the DB
	public Story createStory(String title, String creatorID, Prompt storyPrompt) {
		// Insert new story entry into DB story table 
		int storyAdded = DatabaseManager.executeUpdate(
			new QueryBuilder(QueryType.INSERT)
			.addColumns(STORY_TABLE.STORYTITLE, STORY_TABLE.USERID)
			.addTables(DatabaseTable.STORY_TABLE)
			.addValues( QueryBuilder.toSQLChar(title), QueryBuilder.toSQLChar(creatorID) )
			.value() );	
		
		// Check that it was added & do necessary Story object creating
		if (storyAdded == 1) {
			
			//Create story object with this id & put it into hashmap
			String storyIDAdded = DatabaseManager.executeExactQuery(
				new QueryBuilder(QueryType.SELECTION)
				.addColumns(STORY_TABLE.STORYID)
				.addTables(DatabaseTable.STORY_TABLE)
				.addConditions(QueryCondition.AND, STORY_TABLE.STORYTITLE, QueryCondition.EQ, String.valueOf(title) )
				.addConditions(QueryCondition.AND, STORY_TABLE.USERID, QueryCondition.EQ, String.valueOf(creatorID) )
				.value() );	
			
			Story newStory = new Story(creatorID, Integer.valueOf(storyIDAdded), storyPrompt);
			m_allStories.put(Integer.valueOf(storyIDAdded), newStory);

			// If story has a prompt, update this in the DB
			if (storyPrompt!=null){
				DatabaseManager.executeUpdate(
					new QueryBuilder(QueryType.UPDATE)
					.addColumns(STORY_TABLE.PROMPTID)
					.addTables(DatabaseTable.STORY_TABLE)
					.addValues(String.valueOf(storyPrompt.getID()))
					.addConditions(QueryCondition.AND, STORY_TABLE.STORYID, QueryCondition.EQ, storyIDAdded )
					.value() );	
			}
			return newStory;
		}
		
		return null;
	}
	
	// Returns the story object with storyID
	public Story getStoryByID(int storyID) {
		
		Prompt promptForStory;
		
		Story storyToGet = m_allStories.get(Integer.valueOf(storyID));
		
		if (storyToGet==null){ //Need to grab from DB & create appropriate story object for it
			
			String storyCreatorID = DatabaseManager.executeExactQuery(
				new QueryBuilder(QueryType.SELECTION)
				.addColumns(STORY_TABLE.USERID)
				.addTables(DatabaseTable.STORY_TABLE)
				.addConditions(QueryCondition.AND, STORY_TABLE.STORYID, QueryCondition.EQ, storyID )
				.value() );	
	
			String promptIDString = DatabaseManager.executeExactQuery(
					new QueryBuilder(QueryType.SELECTION)
					.addColumns(STORY_TABLE.PROMPTID)
					.addTables(DatabaseTable.STORY_TABLE)
					.addConditions(QueryCondition.AND, STORY_TABLE.STORYID, QueryCondition.EQ, storyID )
					.value() );	
			
			//Check if story has a prompt
			if ( promptIDString != null && !promptIDString.isEmpty() ){				
				promptForStory = new Prompt(Integer.valueOf(promptIDString));
				storyToGet = new Story(storyCreatorID, storyID, promptForStory);
			}
			
			else { //Story has no prompt
				storyToGet = new Story(storyCreatorID, storyID, null); 
			}
			
			m_allStories.put(Integer.valueOf(storyID), storyToGet);
		
			
			//Check if there are any comments for this story in the DB
			ArrayList<String[]> commentsResults = DatabaseManager.executeQueryWithColumnNames(
				new QueryBuilder(QueryType.SELECTION)
				.addTables(DatabaseTable.COMMENT_TABLE)
				.addConditions(QueryCondition.AND, COMMENT_TABLE.STORYID, QueryCondition.EQ, String.valueOf(storyID) )
				.value() );		
		
			ArrayList<String> commentIDsList = DatabaseManager.getValuesInColumn( commentsResults, COMMENT_TABLE.COMMENTID );
		
			//Create relevant comment objects & update story's comments HashMap 	
			for( int i = 1; i < commentIDsList.size(); i++ ) {
				int idForComment = Integer.parseInt(commentIDsList.get(i));
				Comment newComment = new Comment(idForComment);
				storyToGet.getCommentsHashMap().put(idForComment, newComment);
		}
			
	}
		
		return storyToGet;
	}
	
	//Returns the top N voted stories (or just all the stories if there are less than N)
	public ArrayList<Story> getTopVotedStories(int topN) {
		
		int top = topN;	

		ArrayList<Story> topNVotedStories = new ArrayList<Story>(top);
		
		ArrayList<String[]> allStoriesResult = DatabaseManager.executeQueryWithColumnNames(
				new QueryBuilder(QueryType.SELECTION)
				.addColumns(UPVOTE_TABLE.STORYID) 
				.addTables(DatabaseTable.UPVOTE_TABLE)
				.addAdditionalCriteria("GROUP BY STORYID")
				.addAdditionalCriteria("ORDER BY COUNT(*) DESC")
				.value() );
		
		if (top > (allStoriesResult.size()) ) {  //if topN is greater than the number of stories in our DB
			top = allStoriesResult.size()-1;
		}
		
		ArrayList<String> storyIDList = DatabaseManager.getValuesInColumn( allStoriesResult, STORY_TABLE.STORYID );
		
		for (int i = 0; i < top; i++){
			Story story = this.getStoryByID(Integer.valueOf(storyIDList.get(i)));
			topNVotedStories.add(story);
		}
		
		return topNVotedStories;	
	}
	
	//Returns the top N viewed stories
	public ArrayList<Story> getTopViewedStories(int topN){
		
		int top = topN;
		
		ArrayList<Story> topNViewedStories = new ArrayList<Story>(topN);
		
		ArrayList<String[]> allStoriesResult = DatabaseManager.executeQueryWithColumnNames(
				new QueryBuilder(QueryType.SELECTION)
				.addColumns(STORY_TABLE.STORYID)
				.addTables(DatabaseTable.STORY_TABLE)
				.addAdditionalCriteria("ORDER BY viewcount DESC")
				.value() );
		
		if (top > (allStoriesResult.size()-1) ) {  //if topN is greater than the number of stories in our DB
			top = allStoriesResult.size()-1; //don't rly know why this has to be -1 but it has to be for the for loop to work correctly
		}
		
		ArrayList<String> storyIDList = DatabaseManager.getValuesInColumn( allStoriesResult, STORY_TABLE.STORYID );
		
		for (int i = 0; i < top; i++){
			Story story = this.getStoryByID(Integer.valueOf(storyIDList.get(i)));
			topNViewedStories.add(story);
		}
		
		return topNViewedStories;
	}
	
	
	// Master method for advanced searching (can search by author, title keywords, tags)
	public ArrayList<DatabaseObjects> advancedSearch(String titleKeyword, String author, String[] tags, String sortMethod){
		QueryBuilder searchQuery =	new QueryBuilder(QueryType.SELECTION)
	//								.addColumns(DatabaseTable.STORY_TABLE + "." + STORY_TABLE.STORYID)
									.addTables(DatabaseTable.STORY_TABLE, DatabaseTable.TAG_TABLE, DatabaseTable.POST_TABLE, DatabaseTable.COMMENT_TABLE, DatabaseTable.UPVOTE_TABLE);
		
		if ( titleKeyword != null && !titleKeyword.trim().isEmpty() ){
			searchQuery.addConditions(QueryCondition.AND, STORY_TABLE.STORYTITLE, QueryCondition.LIKE, "%" + titleKeyword + "%");
			
		}
		
		if (author != null && !author.trim().isEmpty() ){
			searchQuery.addConditions(QueryCondition.AND, DatabaseTable.STORY_TABLE + "." + STORY_TABLE.USERID, QueryCondition.LIKE, "%" + author + "%");	
		//	searchQuery.addAdditionalCriteria("OR (STORY_TABLE.storyid = POST_TABLE.storyid AND POST_TABLE.userid LIKE '%" + author + "%')");
		}
	

		if ( tags != null && tags.length != 0 ){
			searchQuery.addConditions(QueryCondition.AND, DatabaseTable.STORY_TABLE + "." + STORY_TABLE.STORYID, QueryCondition.EQ, DatabaseTable.TAG_TABLE + "." + TAG_TABLE.STORYID);
			searchQuery.addConditions(QueryCondition.AND, TAG_TABLE.TAG, QueryCondition.LIKE, QueryBuilder.toSQLChar(tags[0]));		
			
			if (tags.length > 1){
				for (int i = 1; i <tags.length; i++){
					searchQuery.addAdditionalCriteria("AND TAG_TABLE.STORYID IN (SELECT STORYID FROM TAG_TABLE WHERE TAG_TABLE.TAG LIKE " + "'%" + tags[i] + "%')" );
				}
			}
		}
		
		searchQuery.addAdditionalCriteria("GROUP BY STORY_TABLE.STORYID");
		
		//Sort results based on sort method
		if (sortMethod.equals("byview")){
			searchQuery.addAdditionalCriteria("ORDER BY STORY_TABLE.VIEWCOUNT DESC"); 
		}
		
		else if (sortMethod.equals("byvote")){
			ArrayList<String[]> allStoryIDsList = DatabaseManager.executeQuery(
					new QueryBuilder(QueryType.SELECTION)
					.addColumns(STORY_TABLE.STORYID)
					.addTables(DatabaseTable.STORY_TABLE)
					.value() );	
				
				for (int i= 0; i < allStoryIDsList.size(); i++){
					int IDstory = Integer.valueOf(allStoryIDsList.get(i)[0]);
				
					String numVotes = DatabaseManager.executeExactQuery(
							new QueryBuilder(QueryType.SELECTION)
							.addColumns("COUNT(*)") 
							.addTables(DatabaseTable.UPVOTE_TABLE)
							.addConditions(QueryCondition.AND, UPVOTE_TABLE.STORYID, QueryCondition.EQ, IDstory )
							.value() );	
									
					int	voteCount = Integer.valueOf(numVotes);
					
					DatabaseManager.executeUpdate(
							new QueryBuilder(QueryType.UPDATE)
							.addColumns(STORY_TABLE.VOTECOUNT)
							.addTables(DatabaseTable.STORY_TABLE)
							.addValues(String.valueOf(voteCount))
							.addConditions(QueryCondition.AND, STORY_TABLE.STORYID, QueryCondition.EQ, IDstory )
							.value() );	
				}
		
			searchQuery.addAdditionalCriteria("ORDER BY STORY_TABLE.VOTECOUNT DESC"); 	
		}
		
		searchQuery.setDistinct( true );
		
		ArrayList<String[]> searchStoriesResult = DatabaseManager.executeQueryWithColumnNames(searchQuery.value());
		ArrayList<DatabaseObjects> storyList = new ArrayList<DatabaseObjects>();
		
		if( searchStoriesResult != null ) {
			for (int i= 1; i < searchStoriesResult.size(); i++){
				int IDstory = Integer.valueOf(searchStoriesResult.get(i)[0]);
				Story story;
				story = this.getStoryByID(IDstory);
				storyList.add(story);
			}
		}		
		return storyList;
		
	}
	
	public ArrayList<Prompt> getAllPrompt() {
		ArrayList<Prompt> prompts = new ArrayList<Prompt>();
		ArrayList<String[]> promptID = DatabaseManager.executeQuery(
			new QueryBuilder(QueryType.SELECTION)
			.addColumns(PROMPT_TABLE.PROMPTID)
			.addTables(DatabaseTable.PROMPT_TABLE)
			.addConditions(QueryCondition.AND, PROMPT_TABLE.ACTIVE, QueryCondition.EQ, 1 )
			.addAdditionalCriteria("ORDER BY TIMESTAMP ASC")
			.value() );
		
		if( promptID != null ) {
			for( int i = 0; i < promptID.size(); i++ ) {
				prompts.add( new Prompt( Integer.parseInt( promptID.get( i )[0]) ) );
			}
		}
		return prompts;
	}
	
	@Override
	public void update() {}
	
	public String getStorylistNameFromId(String listid) {
	    ArrayList<String[]> name = DatabaseManager.executeQuery(
            new QueryBuilder(QueryType.SELECTION)
            .addColumns(STORYLISTNAME_TABLE.NAME)
            .addTables(DatabaseTable.STORYLISTNAME_TABLE)
            .addConditions(QueryCondition.AND, STORYLISTNAME_TABLE.LISTID, QueryCondition.EQ, listid )
            .value() );
	    
	    if (name != null) {
	        return name.get(0)[0];
	    }
	    
	    return null;
	}

	public String getStorylistOwner(String listid) {
        ArrayList<String[]> userid = DatabaseManager.executeQuery(
            new QueryBuilder(QueryType.SELECTION)
            .addColumns(STORYLISTNAME_TABLE.USERID)
            .addTables(DatabaseTable.STORYLISTNAME_TABLE)
            .addConditions(QueryCondition.AND, STORYLISTNAME_TABLE.LISTID, QueryCondition.EQ, listid )
            .value() );
        
        if (userid != null && userid.size() > 0) {
            return userid.get(0)[0];
        } else {
            return null;
        }
	}
	
	//Returns the storylists of a particular user
    public ArrayList<Storylist> getStorylistsOfUser(String userid){
        
        ArrayList<Storylist> storylists = new ArrayList<Storylist>();
        
        ArrayList<String[]> listIds = DatabaseManager.executeQuery(
                new QueryBuilder( QueryType.SELECTION )
                .addColumns( STORYLISTNAME_TABLE.LISTID )
                .addColumns( STORYLISTNAME_TABLE.NAME )
                .addTables( DatabaseTable.STORYLISTNAME_TABLE )
                .addConditions( QueryCondition.AND, STORYLISTNAME_TABLE.USERID, QueryCondition.EQ, userid )
                .value() );
        
        if( listIds != null ) {
            for( int i = 0; i < listIds.size(); i++ ) {
                String[] list = listIds.get( i );
                int listid = Integer.parseInt( list[0] );
                String name = list[1];

                ArrayList<String[]> storyCnt = DatabaseManager.executeQuery(
                    new QueryBuilder( QueryType.SELECTION )
                    .addColumns( "COUNT(" + STORYLIST_TABLE.STORYID + ")" )
                    .addTables( DatabaseTable.STORYLIST_TABLE )
                    .addConditions( QueryCondition.AND , STORYLIST_TABLE.LISTID, QueryCondition.EQ, listid));
                int count = Integer.parseInt( storyCnt.get(0)[0] );
                
                storylists.add( new Storylist (listid, userid, name, count) );
            }           
        }
        
        return storylists;
    }
    
    public boolean createStorylist( String name, String userid ) {
        int created = DatabaseManager.executeUpdate(
            new QueryBuilder(QueryType.INSERT)
            .addColumns( STORYLISTNAME_TABLE.NAME, STORYLISTNAME_TABLE.USERID )
            .addTables( DatabaseTable.STORYLISTNAME_TABLE )
            .addValues( name, userid )
            .value() );    
        
        if (created == 1) {
            return true;
        }
        
        return false;
    }

    public boolean renameStorylist(String listid, String name) {
        int renamed = DatabaseManager.executeUpdate(
            new QueryBuilder( QueryType.UPDATE )
            .addColumns( STORYLISTNAME_TABLE.NAME )
            .addTables( DatabaseTable.STORYLISTNAME_TABLE )
            .addValues( name )
            .addConditions( QueryCondition.AND, STORYLISTNAME_TABLE.LISTID, QueryCondition.EQ, listid)
            .value() );
        
        if ( renamed == 1 ) {
            return true;
        }
        
        return false;
    }

    public boolean deleteStorylist(String listid) {
        int deleted = DatabaseManager.executeUpdate(
            new QueryBuilder( QueryType.DELETE )
            .addTables( DatabaseTable.STORYLISTNAME_TABLE )
            .addConditions( QueryCondition.AND, STORYLISTNAME_TABLE.LISTID, QueryCondition.EQ, listid)
            .value() );
        
        if ( deleted == 1 ) {
            return true;
        }
        
        return false;
    }

    public ArrayList<Storylist> getListsToAddStoryTo(String userid,
            String storyid) {
        ArrayList<Storylist> storylists = new ArrayList<Storylist>();
        
        ArrayList<String[]> listIds = DatabaseManager.executeQuery(
                new QueryBuilder( QueryType.SELECTION )
                .addColumns( STORYLISTNAME_TABLE.LISTID )
                .addColumns( STORYLISTNAME_TABLE.NAME )
                .addTables( DatabaseTable.STORYLISTNAME_TABLE )
                .addConditions( QueryCondition.AND, STORYLISTNAME_TABLE.USERID, QueryCondition.EQ, userid )
                .value() );
        
        if( listIds != null ) {
            for( int i = 0; i < listIds.size(); i++ ) {
                String[] list = listIds.get( i );
                int listid = Integer.parseInt( list[0] );
                String name = list[1];

                ArrayList<String[]> check = DatabaseManager.executeQuery(
                    new QueryBuilder( QueryType.SELECTION )
                    .addColumns( STORYLIST_TABLE.LISTID )
                    .addTables( DatabaseTable.STORYLIST_TABLE )
                    .addConditions( QueryCondition.AND , STORYLIST_TABLE.LISTID, QueryCondition.EQ, listid)
                    .addConditions( QueryCondition.AND , STORYLIST_TABLE.STORYID , QueryCondition.EQ , storyid));
                
                if (check != null && check.size() == 0) {
                    storylists.add( new Storylist (listid, userid, name, -1) );                   
                }
            }           
        }
        
        return storylists;
    }

    public boolean addStoryToStorylist(String list, String storyid) {
        int added = DatabaseManager.executeUpdate(
            new QueryBuilder( QueryType.INSERT )
            .addColumns( STORYLIST_TABLE.LISTID, STORYLIST_TABLE.STORYID )
            .addTables( DatabaseTable.STORYLIST_TABLE )
            .addValues( list, storyid ));
        
        if (added == 1)
            return true;
        
        return false;
    }

    public boolean removeStoryFromStorylist(String storyid, String listid) {
        int removed = DatabaseManager.executeUpdate(
            new QueryBuilder( QueryType.DELETE )
            .addTables( DatabaseTable.STORYLIST_TABLE )
            .addConditions(QueryCondition.AND, STORYLIST_TABLE.STORYID, QueryCondition.EQ, storyid)
            .addConditions(QueryCondition.AND, STORYLIST_TABLE.LISTID, QueryCondition.EQ, listid));
        
        if (removed == 1)
            return true;
        
        return false;
    }
}
